package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.GraduateProjectStatus;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;

public final class GraduateProjectStatusDao {
    private static GraduateProjectStatusDao graduateProjectStatusDao;

    private GraduateProjectStatusDao() {
    }

    public static GraduateProjectStatusDao getInstance() {
        if (graduateProjectStatusDao == null) {
            graduateProjectStatusDao = new GraduateProjectStatusDao();
        }
        return graduateProjectStatusDao;
    }

    public static Collection<GraduateProjectStatus> findAll(Connection connection) throws SQLException {
        Collection<GraduateProjectStatus> graduateProjectStatuses = new HashSet<GraduateProjectStatus>();
        //准备预编译的语句
        int totalNum = CommonDao.count(connection, "GraduateProjectStatus");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM GraduateProjectStatus");
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        //遍历resultSet
        while (resultSet_select.next()) {
            //根据id查找结果创建GraduateProjectStatus对象
            GraduateProjectStatus graduateProjectStatus = new GraduateProjectStatus(
                    resultSet_select.getInt("id"),
                    resultSet_select.getString("description"),
                    resultSet_select.getString("no"),
                    resultSet_select.getString("remarks"));
            graduateProjectStatuses.add(graduateProjectStatus);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        return graduateProjectStatuses;
    }


    //根据id，查找学院
    public GraduateProjectStatus find(Integer id, Connection connection) throws SQLException {

        String findGraduateProjectStatus_sql = "select * from GraduateProjectStatus where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(findGraduateProjectStatus_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找学院
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建GraduateProjectStatus对象
        GraduateProjectStatus graduateProjectStatus = new GraduateProjectStatus(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        //关闭
        JdbcHelper.close(resultSet,pstmt);
        return graduateProjectStatus;
    }

    //修改
    public boolean update(GraduateProjectStatus graduateProjectStatus, Connection connection) throws SQLException {

        String updateGraduateProjectStatus_sql = "" +
                "UPDATE GraduateProjectStatus SET no = ?,description = ?,remarks = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateGraduateProjectStatus_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProjectStatus.getNo());
        pstmt.setString(2, graduateProjectStatus.getDescription());
        pstmt.setString(3, graduateProjectStatus.getRemarks());
        pstmt.setInt(4, graduateProjectStatus.getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        return  affectedRowNum> 0;
    }

    //添加学院
    public boolean add(GraduateProjectStatus graduateProjectStatus, Connection connection) throws SQLException {

        String addGraduateProjectStatus_sql = "" +
                "INSERT INTO GraduateProjectStatus(no,description,remarks) VALUES" + "(?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGraduateProjectStatus_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProjectStatus.getNo());
        pstmt.setString(2, graduateProjectStatus.getDescription());
        pstmt.setString(3, graduateProjectStatus.getRemarks());
        //添加学院 并判断是否添加成功
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        return  affectedRowNum> 0;
    }

    //根据GraduateProjectStatus对象删除
    public static boolean delete(GraduateProjectStatus graduateProjectStatus, Connection connection) throws SQLException {

        String deleteGraduateProjectStatus_sql = "delete from GraduateProjectStatus where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGraduateProjectStatus_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProjectStatus.getNo());
        //根据id删除学院 并判断是否删除成功
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        return  affectedRowNum> 0;
    }
}
